package com.situ.water_BE.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.situ.water_BE.pojo.BeFlow;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import javax.annotation.Resource;
import java.util.List;
import java.util.Map;


/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author Angel
 * @since 2020-12-01
 */

@Mapper
@Resource
public interface BeFlowMapper extends BaseMapper<BeFlow> {
    @Select("select ID,StepName from BE_Flow")
    public List<BeFlow> queryAll();

    @Select("SELECT id FROM be_flow where Type${type}=(select Type${type} FROM be_flow where id=#{buZou})")
    public Integer queryId(String type,Integer buZou);

    @Select("SELECT id FROM be_flow where Type${type}=(select (Type${type})+1 FROM be_flow where id=#{buZou})")
    public Integer queryIdJia(String type,Integer buZou);

    @Select("SELECT id FROM be_flow where Type${type}=(select (Type${type})-1 FROM be_flow where id=#{buZou})")
    public Integer queryIdJian(String type,Integer buZou);

    @Select("SELECT f.StepName,(case when f.Type1 is not null then count(case when o.OrderType=1 then f.Type1 end) else null end) as type1,\n" +
            "case when f.Type2 is not null then count(case when o.OrderType=2 then f.Type2 end) else null  end as 分户,\n" +
            "case when f.Type3 is not null then count(case when o.OrderType=3 then 1 end) else null  end as 过户,\n" +
            "case when f.Type4 is not null then count(case when o.OrderType=4 then 1 end) else null  end as 代扣,\n" +
            "case when f.Type5 is not null then count(case when o.OrderType=5 then 1 end) else null  end as 换表,\n" +
            "case when f.Type6 is not null then count(case when o.OrderType=6 then 1 end) else null  end as 重签,\n" +
            "case when f.Type7 is not null then count(case when o.OrderType=7 then 1 end) else null  end as 销户,\n" +
            "count(*) as 总数\n" +
            "FROM be_flow f LEFT JOIN be_order o on f.id=o.StepID LEFT JOIN  be_orderuser u \n" +
            "on  o.OrderNo=u.OrderNo GROUP BY f.id UNION\n" +
            "\n" +
            "SELECT '待办总数' as StepName,sum(f.`新户`),sum(f.`分户`),sum(f.`过户`),sum(f.`代扣`),sum(f.`换表`),sum(f.`重签`),sum(f.`销户`),sum(f.`总数`)from (\n" +
            "SELECT f.StepName,case when f.Type1 is not null then count(case when o.OrderType=1 then f.Type1 end) else null  end as 新户,\n" +
            "case when f.Type2 is null then '' else count(case when o.OrderType=2 then f.Type2 end) end as 分户,\n" +
            "case when f.Type3 is null then '' else count(case when o.OrderType=3 then 1 end) end as 过户,\n" +
            "case when f.Type4 is null then '' else count(case when o.OrderType=4 then 1 end) end as 代扣,\n" +
            "case when f.Type5 is null then '' else count(case when o.OrderType=5 then 1 end) end as 换表,\n" +
            "case when f.Type6 is null then '' else count(case when o.OrderType=6 then 1 end) end as 重签,\n" +
            "case when f.Type7 is null then '' else count(case when o.OrderType=7 then 1 end) end as 销户,\n" +
            "count(*) as 总数\n" +
            "FROM be_flow f LEFT JOIN be_order o on f.id=o.StepID LEFT JOIN  be_orderuser u \n" +
            "on  o.OrderNo=u.OrderNo where f.id>0 GROUP BY f.id ) f ")
    public List<Map> countOrderUser();
}
